--------------------------------------------------------------------------------
-- @name: rebuild and logical reads
-- @author: dion cho
-- @note: test case for the increase of the logical reads by index rebuild
--------------------------------------------------------------------------------

set pages 10000
set lines 200
set timing off
set trimspool on

-- version
select * from v$version where rownum = 1;

drop table t1 purge;
drop table t2 purge;

create table t1(c1 int, c2 int);

create table t2(c1 int, c2 int);

create index t2_n1 on t2(c1);

insert into t1
select level, level 
from dual
connect by level <= 200000;

insert into t2
select level, level
from dual
connect by level <= 200000;

commit;

exec dbms_stats.gather_table_stats(user, 't1');
exec dbms_stats.gather_table_stats(user, 't2');

-- before index rebuild
select /*+ gather_plan_statistics 
            leading(t1) use_nl(t2) index(t2) */
  count(t2.c2)
from 
  t1, t2
where t1.c1 = t2.c1
      and t1.c1 > 0
;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

-- rebuild the index
alter index t2_n1 rebuild;


select /*+ gather_plan_statistics 
            leading(t1) use_nl(t2) index(t2) */
  count(t2.c2)
from 
  t1, t2
where t1.c1 = t2.c1
      and t1.c1 > 0
;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));


-- session snapshot report

-- session #1
drop index t2_n1;
create index t2_n1 on t2(c1);

exec tpack.begin_session_snapshot;

select /*+ gather_plan_statistics 
            leading(t1) use_nl(t2) index(t2) */
  count(t2.c2)
from 
  t1, t2
where t1.c1 = t2.c1
      and t1.c1 > 0
;

-- session #2
alter index t2_n1 rebuild;

-- session #1
exec tpack.add_session_snapshot;

select /*+ gather_plan_statistics 
            leading(t1) use_nl(t2) index(t2) */
  count(t2.c2)
from 
  t1, t2
where t1.c1 = t2.c1
      and t1.c1 > 0
;

exec tpack.add_session_snapshot;

col type format a10
break on type skip 1
col item format a20
col deltas format a20

select * from table(tpack.session_snapshot_report);


-- latch profile report

-- before rebuild
-- session #1
exec dbms_application_info.set_client_info('SESSION1');

drop index t2_n1;
create index t2_n1 on t2(c1);

-- session #2
col sid new_value sid
select sid from v$session where client_info = 'SESSION1';

exec tpack.wait_for_signal;

col sid format 9999
col latch_name format a30
col obj_why format a15
set lines 200
select * from table(tpack.latch_profile_report(&sid, 'LATCH_NAME, OBJ_WHY', 'cache buffers chains'));


-- session #1
exec tpack.signal;
select /*+ gather_plan_statistics 
            leading(t1) use_nl(t2) index(t2) */
  count(t2.c2)
from 
  t1, t2
where t1.c1 = t2.c1
      and t1.c1 > 0
;

-- after rebuild
-- session #1
alter index t2_n1 rebuild;

-- session #2
col sid new_value sid
select sid from v$session where client_info = 'SESSION1';

exec tpack.wait_for_signal;

col sid format 9999
col latch_name format a30
col obj_why format a15
set lines 200
select * from table(tpack.latch_profile_report(&sid, 'LATCH_NAME, OBJ_WHY', 'cache buffers chains'));

-- session #1
exec tpack.signal;
select /*+ gather_plan_statistics 
            leading(t1) use_nl(t2) index(t2) */
  count(t2.c2)
from 
  t1, t2
where t1.c1 = t2.c1
      and t1.c1 > 0
;


-- block dump 4/1660 
select * from table(tpack.block_dump(&f, &b));

select * from table(tpack.index_tree_dump('tpack', 't2_n1'));

select header_file, header_block from dba_segments where owner = user and segment_name = 'T2_N1';
-- Bug 6455161  Higher CPU / Higher "cache buffer chains" latch gets / Higher "consistent gets" after truncate/Rebuild



@capture_off
